﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DAHocVien_Thi_CASEC:DbObject
    {
        protected SqlConnection conn;
        public DAHocVien_Thi_CASEC(SqlConnection Conection)
        {
            conn = Conection;
        }
        public DataSet get_diem_thi_hoc_vien(int ip_i_hocvien_id)
        {
            DataSet op_ds_bang_diem_hv = new DataSet();
            SqlParameter[] parameters =  {	
                                             new SqlParameter("@HocVienId", SqlDbType.Int,4)
										 };
            // set the 0values
            parameters[0].Value = ip_i_hocvien_id;
            // create the parameter
            op_ds_bang_diem_hv = RunProcedure("pr_HocVien_Thi_CASEC_get_diem_thi_hv", parameters, "TB_HocVien_Thi_CASEC", conn);
            return op_ds_bang_diem_hv;
        }

        public int cap_nhat_diem_casec_cho_hv(int HocVienID, DateTime L5A_Date
                    , string L5A_Code, string L5A_Password, string L5A_StudentName, decimal L5A_S1_Vocabulary
                    , decimal L5A_S2_Ex_Gramma, decimal L5A_S3_Listen1, decimal L5A_S4_Listen2, decimal L5A_Total
                    , decimal L5A_TOEFL, decimal L5A_TOEIC, int L5A_Level_CASEC, decimal L5A_Speaking, int L5A_Level_Speaking)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	    new SqlParameter("@HocVienID", SqlDbType.Int ,4),
                                                new SqlParameter("@L5A_Date", SqlDbType.DateTime),
                                                new SqlParameter("@L5A_Code", SqlDbType.VarChar, 50),
                                                new SqlParameter("@L5A_Password", SqlDbType.VarChar, 50),
                                                new SqlParameter("@L5A_StudentName", SqlDbType.NVarChar, 50 ),
                                                new SqlParameter("@L5A_S1_Vocabulary", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_S2_Ex_Gramma", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_S3_Listen1", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_S4_Listen2", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_Total", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_TOEFL", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_TOEIC", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_Level_CASEC", SqlDbType.Int)  ,
                                                new SqlParameter("@L5A_Speaking", SqlDbType.Decimal),
                                                new SqlParameter("@L5A_Level_Speaking", SqlDbType.Int, 4)
										};
            // set the values
            parameters[0].Value = HocVienID;
            parameters[1].Value = L5A_Date;
            parameters[2].Value = L5A_Code;
            parameters[3].Value = L5A_Password;
            parameters[4].Value = L5A_StudentName;
            parameters[5].Value = L5A_S1_Vocabulary;
            parameters[6].Value = L5A_S2_Ex_Gramma;
            parameters[7].Value = L5A_S3_Listen1;
            parameters[8].Value = L5A_S4_Listen2;
            parameters[9].Value = L5A_Total;
            parameters[10].Value = L5A_TOEFL;
            parameters[11].Value = L5A_TOEIC;
            parameters[12].Value = L5A_Level_CASEC;
            parameters[13].Value = L5A_Speaking;
            parameters[14].Value = L5A_Level_Speaking;
            // run the procedure
            RunProcedure("sp_HocVien_Thi_CASEC_updateKetQua", parameters, out numAffected, conn);

            return numAffected;
        }
        
        public DataSet loadHocVienThiCasec(string strHoTenHocVien
                                    , string strDienThoaiHocvien, DateTime datNgayCaptk, int TrangthaiId)
        {
            DataSet v_ds = new DataSet();
            SqlParameter[] parameters =  {	
                                             new SqlParameter("@HoTenHocVien", SqlDbType.NVarChar, 50)
                                           , new SqlParameter("@TrangThaiId", SqlDbType.Int, 4)
                                           , new SqlParameter("@NgayCapTaiKhoan", SqlDbType.DateTime)
                                           , new SqlParameter("@DienThoaiHV", SqlDbType.VarChar, 20)
										 };
            // set the 0values
            parameters[0].Value = strHoTenHocVien;
            parameters[1].Value = TrangthaiId;
            parameters[2].Value = datNgayCaptk;
            parameters[3].Value = strDienThoaiHocvien;
            // create the parameter
            v_ds = RunProcedure("pr_HocVien_Thi_CASEC_LoadHocVienThiCasec", parameters, "TB_HocVien_Thi_CASEC", conn);
            return v_ds;
        }
        public int themThongTinTaiKhoanCasec(int hocVienId
                                   , DateTime datNgayCapTKCasec
                                   , string strAccountCasec
                                   , string strPasswordCasec
                                   , string strAccCasecOK
                                   , string strHoTenHocVien
                                   , int intNguoiDungId)
        {
            int result = 0;
            SqlParameter[] parameters =  {	 
                                             
                new SqlParameter("@HocVienId", SqlDbType.Int ),
                new SqlParameter("@datNgayCapTKCasec", SqlDbType.DateTime ),
                new SqlParameter("@strAccountCasec", SqlDbType.VarChar, 20 ), 
                new SqlParameter("@strPasswordCasec", SqlDbType.VarChar, 100 ),
                new SqlParameter("@strAccCasecOK", SqlDbType.VarChar, 2),
                new SqlParameter("@intNguoiDungId", SqlDbType.Int),
                new SqlParameter("@strHoTenHocVien", SqlDbType.NVarChar, 50)
                };
            // set the 0values
            parameters[0].Value = hocVienId;
            parameters[1].Value = datNgayCapTKCasec;
            parameters[2].Value = strAccountCasec;
            parameters[3].Value = strPasswordCasec;
            parameters[4].Value = strAccCasecOK;
            parameters[5].Value = intNguoiDungId;
            parameters[6].Value = strHoTenHocVien;

            // create the parameter

            int value = RunProcedure("sp_TaiKhoanCASEC_Them_Account_Casec", parameters, out result, conn); ;
            return result;
        }
    }
}
